package com.hbyj.db.function;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;

import net.sf.json.JSONArray;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import com.hbyj.db.factory.CDBConnection;
import com.hbyj.db.factory.HibernateSessionFactory;
import com.hbyj.hbm.vo.Jbqk01temp;
import com.hbyj.util.ChangeTool;
import com.hbyj.util.CopyTblTool;


/**
 * 数据库Hibernate操作
 * 
 * @author lc
 * 
 */
public class DbTools {
	public static Session getSession() {
		return HibernateSessionFactory.getSession();
	}

	/**
	 * author cuiyi
	 * time 2012-7-15
	 * disc 分页查询 HQL
	 * @param first
	 * @param max
	 * @param hql
	 * @return
	 */
	public static List findPageHQL(int first, int max, String hql) {
		List list = null;
		try{
			Session session = HibernateSessionFactory.getSession();

			Transaction tran = session.beginTransaction();

			list = session.createQuery(hql).setFirstResult(first)
					.setMaxResults(max).list();

			tran.commit();
			HibernateSessionFactory.closeSession();
		}catch (Exception e) {
			
			// TODO: handle exception
			System.out.println(e);
		}
		

		return list;
	}
	/**
	 * author cuiyi
	 * time 2012-7-15
	 * disc 获取记录总条数
	 * @param hql
	 * @return
	 */
	public static int getDataSQLCount(String hql) {
		int count = 0;

		Session session = HibernateSessionFactory.getSession();
		Transaction tran = session.beginTransaction();

		Integer cos = (Integer) session.createSQLQuery(hql).uniqueResult();

		count = cos.intValue();

		tran.commit();
		session.close();
		return count;
	}
	/**
	 * author cuiyi
	 * time 2012-8-21
	 * disc 
	 * @param hql
	 * @return
	 */
	public static int getDataHQLCount(String hql) {
		int count = 0;

		Session session = HibernateSessionFactory.getSession();
		//Transaction tran = session.beginTransaction();

		try{
			Query qy = session.createQuery(hql);
			count  = Integer.parseInt(qy.list().get(0).toString());
		}catch (Exception e) {
			// TODO: handle exception
			System.out.print(e);
		}
		
		//Integer cos = (Integer) session.createQuery(hql).uniqueResult();
		//count = cos.intValue();

		//tran.commit();
		session.close();
		return count;
	}
	/**
	 * 存储对象
	 * 
	 * @param obj
	 * @return 存储成功则返回true，否则为false
	 */
	public static boolean saveObj(Object obj) {
		Session session = getSession();
		session.beginTransaction();
		try {
			session.save(obj);
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
			return false;
		} finally {
			session.close();
		}
		return true;
	}

	/**
	 * 册除对象
	 * 
	 * @param 删除sql
	 * @return 删除成功返回true
	 */
	public static boolean delete(String deletesql) {
		Session session = getSession();
		Transaction tx = session.beginTransaction();
		Query query = null;
		try {
			query = session.createQuery(deletesql);
			query.executeUpdate();
			tx.commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
			return false;
		} finally {
			session.close();
		}
		return true;
	}

	/**
	 * 册除对象
	 * 
	 * @param obj
	 * @return 删除成功返回true
	 */
	public static boolean deleteObj(Object obj) {
		Session session = getSession();
		session.beginTransaction();
		try {
			session.delete(obj);
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
			return false;
		} finally {
			session.close();
		}
		return true;
	}

	/**
	 * 更新对象
	 * 
	 * @param obj
	 * @return 更新成功成功返回true
	 */
	public static boolean updateObj(Object obj) {
		Session session = getSession();
		session.beginTransaction();
		try {
			session.update(obj);
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
			return false;
		} finally {
			session.close();
		}
		return true;
	}

	/**
	 * author cuiyi time Nov 16, 2009
	 */
	public static boolean updateBySql(String sql) {
		Session session = getSession();
		Transaction t_tx = null;
		boolean res = false;
		try {
			t_tx = session.beginTransaction();
			Connection t_conn = session.connection();
			PreparedStatement t_pStmt = t_conn.prepareStatement(sql);
			t_pStmt.executeUpdate();
			res = true;

		} catch (Exception e) {
			e.printStackTrace();
			t_tx.rollback();
		} finally {
			t_tx.commit();
			session.close();
		}
		return res;
	}

	/**
	 * 存储对象
	 * 
	 * @param obj
	 * @return 存储成功则返回true，否则为false
	 */
	public static boolean saveOrUpdateObj(Object obj) {
		Session session = getSession();
		session.beginTransaction();
		try {
			session.saveOrUpdate(obj);
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
			return false;
		} finally {
			session.close();
		}
		return true;
	}

	/**
	 * 根据对象名、对象的属性名查询对象
	 * 
	 * @param objName
	 *            对象名（类名）
	 * @param propertyName
	 *            属性名
	 * @param value
	 *            属性值
	 * @return
	 */
	public static List findByProperty(String objName, String propertyName,
			Object value) {
		Session session = getSession();
		try {
			String queryString = "from " + objName + " as model where model."
					+ propertyName + "= ?";
			Query queryObject = session.createQuery(queryString);
			queryObject.setParameter(0, value);
			return queryObject.list();
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	/**
	 * 根据对象名、对象的属性名查询对象 应用于复合ID
	 * 
	 * @param objName
	 *            对象名（类名）
	 * @param propertyName
	 *            属性名
	 * @param value
	 *            属性值
	 * @return List
	 */
	public static List findByPropertyForId(String objName, String propertyName,
			Object value) {
		Session session = getSession();
		try {
			String queryString = "from " + objName
					+ " as model where model.id." + propertyName + "= ?";
			Query queryObject = session.createQuery(queryString);
			queryObject.setParameter(0, value);
			return queryObject.list();
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	/**
	 * 根据对象名、对象的属性名查询对象
	 * 
	 * @param objName
	 *            对象名（类名）
	 * @param propertyName
	 *            属性名
	 * @param value
	 *            属性值
	 * @return Object
	 */
	public static Object findByPro(String objName, String propertyName,
			Object value) {
		Session session = getSession();
		try {
			String queryString = "from " + objName + " as model where model."
					+ propertyName + "= ?";
			Query queryObject = session.createQuery(queryString);
			queryObject.setParameter(0, value);
			Object obj = null;
			if (queryObject.list().iterator().hasNext()) {
				obj = (Object) queryObject.list().iterator().next();
			}
			return obj;
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	/**
	 * 根据对象名、对象的属性名查询某一字段值
	 * 
	 * @param objName
	 *            对象名（类名）
	 * @param propertyName
	 *            属性名
	 * @param value
	 *            属性值
	 * @param FieldName
	 *            要查询的字段名
	 * @return Object
	 */

	public static Object findField(String objName, String propertyName,
			String value, String FieldName) {
		Session session = getSession();
		try {
			String queryString = "select " + FieldName + " from " + objName
					+ " as model where model." + propertyName + "= ?";
			Query queryObject = session.createQuery(queryString);
			queryObject.setParameter(0, value);
			Object obj = null;
			if (queryObject.list().iterator().hasNext()) {
				obj = (Object) queryObject.list().iterator().next();
			}
			return obj;
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	public static Object findObjByHql(String hql) {
		Session session = getSession();
		try {
			Query queryObject = session.createQuery(hql);
			Object obj = null;
			if (queryObject.list().iterator().hasNext()) {
				obj = (Object) queryObject.list().iterator().next();
			}
			return obj;
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	public static Object findObjBySql(String sql) {
		Session session = getSession();
		try {
			Query queryObject = session.createSQLQuery(sql);
			Object obj = null;
			if (queryObject.list().iterator().hasNext()) {
				obj = (Object) queryObject.list().iterator().next();
			}
			return obj;
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	public static Integer getStatResult(String hql) {
		Session session = getSession();
		try {
			Query queryObject = session.createQuery(hql);
			Integer obj = 0;
			if (queryObject.list().iterator().hasNext()) {
				obj = (Integer) queryObject.list().iterator().next();
			}
			return obj;
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	public static Integer getStatResultSql(String sql) {
		Session session = getSession();
		try {
			Query queryObject = session.createSQLQuery(sql);
			Integer obj = 0;
			if (queryObject.list().iterator().hasNext()) {
				obj = (Integer) queryObject.list().iterator().next();
			}
			return obj;
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	/**
	 * 根据对象名以及属性进行模糊查询
	 * 
	 * @param propertyName
	 * @param value
	 * @return
	 */
	public static List findAlikeByProperty(String objName, String propertyName,
			Object value) {
		String queryString = "from " + objName + " as model where model."
				+ propertyName + " like '" + value + "'";
		return findByHql(queryString);

	}

	/**
	 * 根据HQL语句查询
	 * 
	 * @param hql
	 * @return
	 */
	public static List findByHql(String hql) {
		Session session = getSession();
		try {
			Query queryObject = session.createQuery(hql);
			return queryObject.list();
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	/**
	 * 根据SQL语句查询
	 * 
	 * @param sql
	 * @return
	 */
	public static List findBySql(String sql) {
		Session session = getSession();
		
		try {
			Query queryObject = session.createSQLQuery(sql);
			
			return queryObject.list();
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	private static ResultSet rs = null;
	private static Statement stmt = null;
	/**
	 * 根据JDBC查询sql语句
	 * 
	 * @param sql
	 *            标准SQL语句
	 * @return 结果集
	 */
	private static Connection conn = null;

	/**
	 * 关闭数据库连接
	 */
	public void closeDbConnection() {
		try {
			rs.close();
			stmt.close();
			// conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}	
	public static CDBInfo getDBInfo(){
		CDBInfo dbInfo = new CDBInfo();
		Session session = getSession();
	
		Configuration configuration = HibernateSessionFactory.getConfiguration();
		
		String url = configuration.getProperty("url");
		String userName = configuration.getProperty("username");
		String password = configuration.getProperty("password");
		String driveName = configuration.getProperty("driver_class");
		dbInfo.setdBType("SQLServer");
		dbInfo.setConnectionUrl(url);
		dbInfo.setUserName(userName);
		dbInfo.setPassWord(password);
		dbInfo.setDriverName(driveName);
		return dbInfo;
	}
	public static void main(String[] args){
		//convertData();
		CopyTblTool copyTool = new CopyTblTool();
		//源数据库配置
		Session session = getSession();
		Connection connSource = session.connection();
		copyTool.setConnSource(connSource);
		//目标数据库配置
		CDBInfo dbInfo = new CDBInfo();
		//dbInfo.setdBType("SQLServer");
		dbInfo.setdBService("127.0.0.1");
		dbInfo.setdBPort("1433");
		dbInfo.setdBName("master");
		dbInfo.setUserName("sa");
		dbInfo.setPassWord("guest");
		//dbInfo.setDriverName("net.sourceforge.jtds.jdbc.Driver");
		Connection connTarget = CDBConnection.getSQLConnection(dbInfo);
		copyTool.setConnTarget(connTarget);
		copyTool.convertData("JBQK01", "JBQK01","42");
	}
	public static String getYJDCInfoFromServer(CDBInfo dbinfo,String[] tblList,String region){
		String resInfo = "";
		CopyTblTool copyTool = new CopyTblTool();
		//源数据库配置
		Session session = getSession();
		Connection connSource = session.connection();
		copyTool.setConnSource(connSource);
		//目标数据库配置
		Connection connTarget = CDBConnection.getSQLConnection(dbinfo);
		copyTool.setConnTarget(connTarget);
		//////开始拷贝数据表
		for(int i=0;i<tblList.length;i++){
			resInfo += copyTool.convertData(tblList[i], tblList[i],region)+",";
		}
		if(!resInfo.equals(""))
			resInfo.substring(0, resInfo.length()-1);
		return resInfo;
	}
	public static boolean getSketchCopyRes(CDBInfo dbinfo,String tblName,String id,String time,String jbqk0102){
		String resInfo = "";
		CopyTblTool copyTool = new CopyTblTool();
		//目标数据库配置
		Session session = getSession();
		Connection connTarget = session.connection();
		copyTool.setConnTarget(connTarget);
		//源数据库配置
		Connection connSource = CDBConnection.getSQLConnection(dbinfo);
		copyTool.setConnSource(connSource);
		//////开始拷贝数据表
		return copyTool.convertSketch(tblName, tblName, id, time,jbqk0102);
	}
	public static boolean addDwxxByYjdcPb(CDBInfo dbinfo,String id,String time,String jbqk0102){
		String resInfo = "";
		CopyTblTool copyTool = new CopyTblTool();
		//目标数据库配置
		Session session = getSession();
		Connection connTarget = session.connection();
		copyTool.setConnTarget(connTarget);
		//源数据库配置
		Connection connSource = CDBConnection.getSQLConnection(dbinfo);
		copyTool.setConnSource(connSource);
		//////开始拷贝数据表
		String  tblName1 = "wdwzforzhdn";
		String  tblName2 = "wdwzforryd";
		String  tblName3 = "YJDC_GPSLINE";
		String  tblName4 = "YJDC_GPSLINEFORZHD";
		boolean flg1 = copyTool.convertWdwzforzhdn(tblName1, id,jbqk0102);
		boolean flg2= copyTool.convertWdwzforryd(tblName2);
		boolean flg3 = copyTool.convertGPSLINE(tblName3);
		boolean flg4 = copyTool.convertGPSLINEZHD(tblName4, id,jbqk0102);
		return  flg1&&flg2&&flg3&&flg4;
	}
	
	/**
	 * author cuiyi
	 * time 2012-12-7
	 * disc 将结果集转换为地图定位数组
	 * @param list （JBQK01的对象数组）
	 * @return
	 */
	public static List parseBaleInfoObj(List list){
		List rows = new ArrayList();
		Iterator it =list.iterator();
		while(it.hasNext()){
			Jbqk01temp jbqk01 = new Jbqk01temp();
			Object[] obj = (Object[])it.next();
			jbqk01.setJbqk0100(obj[0]==null?"":obj[0].toString());
			jbqk01.setJbqk0110(obj[1]==null?"":obj[1].toString());
			jbqk01.setJbqk0120(obj[2]==null?0:Double.parseDouble(obj[2].toString()));
			jbqk01.setJbqk0130(obj[3]==null?0:Double.parseDouble(obj[3].toString()));
			jbqk01.setJbqk0140(obj[4]==null?"":obj[4].toString());
			jbqk01.setJbqk0150(obj[5]==null?"":obj[5].toString());
			jbqk01.setJbqk0160(obj[6]==null?"":obj[6].toString());
			jbqk01.setJbqk0170(obj[7]==null?"":obj[7].toString());
			jbqk01.setJbqk0180(obj[8]==null?"":obj[8].toString());
			jbqk01.setJbqk0190(obj[9]==null?"":obj[9].toString());
			jbqk01.setJbqk0102(obj[10]==null?"":obj[10].toString());
			rows.add(jbqk01);
		}
		return rows;
	}

}
